using System.IO;
using System.Reflection;
using GrapeCity.Documents.Excel;
using System;
using System.Data;

namespace GrapeCity.Documents.Excel.Examples.Templates.Properties.FilterProperty.CombinedFilter
{
    class Program
    {
        static void Main(string[] args)
        {
			//create a new workbook
			var workbook = new GrapeCity.Documents.Excel.Workbook();
			
			//Load template file Template_Score.xlsx from resource
			var templateFile = GetResourceStream("xlsx\\CombinedFilter.xlsx");
			workbook.Open(templateFile);
			
			#region table order
			{
			    var datasource = new DataTable();
			    datasource.Columns.Add(new DataColumn("oid", typeof(string)));
			    datasource.Columns.Add(new DataColumn("cid", typeof(string)));
			    datasource.Columns.Add(new DataColumn("pid", typeof(string)));
			    datasource.Columns.Add(new DataColumn("count", typeof(double)));
			    datasource.Rows.Add("TF00001", "C001", "R001", 1);
			    datasource.Rows.Add("TF00002", "C001", "T002", 1);
			    datasource.Rows.Add("TF00003", "C001", "W003", 1);
			    datasource.Rows.Add("TF00004", "C001", "C004", 1);
			    datasource.Rows.Add("TF00005", "C001", "O005", 1);
			    datasource.Rows.Add("TF00006", "C002", "R001", 1);
			    datasource.Rows.Add("TF00007", "C002", "T002", 1);
			    datasource.Rows.Add("TF00008", "C002", "W003", 1);
			    datasource.Rows.Add("TF00009", "C002", "C004", 1);
			    datasource.Rows.Add("TF00010", "C002", "O005", 1);
			    datasource.Rows.Add("TF00011", "C003", "W009", 5);
			    datasource.Rows.Add("TF00012", "C003", "R001", 2);
			    datasource.Rows.Add("TF00013", "C003", "T002", 1);
			    datasource.Rows.Add("TF00014", "C003", "W003", 3);
			    datasource.Rows.Add("TF00015", "C004", "L010", 10);
			    datasource.Rows.Add("TF00016", "C005", "B008", 999);
			    datasource.Rows.Add("TF00017", "C006", "C007", 23);
			    datasource.Rows.Add("TF00018", "C007", "N011", 1);
			    datasource.Rows.Add("TF00019", "C007", "G012", 10);
			    datasource.Rows.Add("TF00020", "C008", "P013", 10);
			    datasource.Rows.Add("TF00021", "C008", "P014", 15);
			    datasource.Rows.Add("TF00022", "C008", "S015", 2);
			    workbook.AddDataSource("order", datasource);
			}
			#endregion
			#region table customer
			{
			    var datasource = new DataTable();
			    datasource.Columns.Add(new DataColumn("cid", typeof(string)));
			    datasource.Columns.Add(new DataColumn("name", typeof(string)));
			    datasource.Columns.Add(new DataColumn("country", typeof(string)));
			    datasource.Columns.Add(new DataColumn("age", typeof(double)));
			    datasource.Rows.Add("C001", "Tom", "USA", 4);
			    datasource.Rows.Add("C002", "Jerry", "USA", 2);
			    datasource.Rows.Add("C003", "Mario", "JP", 7);
			    datasource.Rows.Add("C004", "Luigi", "JP", 8);
			    datasource.Rows.Add("C005", "Jolyne", "USA", 19);
			    datasource.Rows.Add("C006", "Enrico", "USA", 54);
			    datasource.Rows.Add("C007", "Kira", "JP", 33);
			    datasource.Rows.Add("C008", "Rohan", "JP", 20);
			    workbook.AddDataSource("customer", datasource);
			}
			#endregion
			#region table product
			{
			    var datasource = new DataTable();
			    datasource.Columns.Add(new DataColumn("pid", typeof(string)));
			    datasource.Columns.Add(new DataColumn("name", typeof(string)));
			    datasource.Columns.Add(new DataColumn("unitprice", typeof(double)));
			    datasource.Rows.Add("R001", "Refrigerator", 3299.9);
			    datasource.Rows.Add("T002", "Television", 899);
			    datasource.Rows.Add("W003", "Washer", 599.9);
			    datasource.Rows.Add("C004", "Computer", 5999.9);
			    datasource.Rows.Add("O005", "Oven", 1388.8);
			    datasource.Rows.Add("D006", "Dishwasher", 2399.9);
			    datasource.Rows.Add("C007", "CD", 13);
			    datasource.Rows.Add("B008", "Blue frog", 1);
			    datasource.Rows.Add("W009", "Wrench", 19.9);
			    datasource.Rows.Add("L010", "Lantern", 45);
			    datasource.Rows.Add("N011", "Nail clippers", 2.99);
			    datasource.Rows.Add("G012", "Gloves", 3.99);
			    datasource.Rows.Add("P013", "Pigment", 99);
			    datasource.Rows.Add("P014", "Pencil", 6.6);
			    datasource.Rows.Add("S015", "Sketchbook", 23.5);
			    workbook.AddDataSource("product", datasource);
			}
			#endregion
			
			//Invoke to process the template
			workbook.ProcessTemplate();
			workbook.Worksheets[0].Range["A:F"].ColumnWidth = 16;
			workbook.Worksheets[0].Range["A3"].ClearComments();
			        
			// Save to an excel file
			workbook.Save("CombinedFilter.xlsx");

        }

		static Stream GetResourceStream(string resourcePath)
        {
            string resource = "CombinedFilter.Resource." + resourcePath.Replace("\\", ".");
            var assembly = typeof(Program).GetTypeInfo().Assembly;
            return assembly.GetManifestResourceStream(resource);
        }

    }
}